package com.code2roc.jarmanage.util;

import com.code2roc.jarmanage.model.SqlModel;
import org.h2.jdbc.JdbcClob;

import java.io.File;
import java.io.Reader;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class DataBaseUtil {

    public static Connection getConnection() throws Exception {
        File file = new File("database");
        Connection conn = DriverManager.getConnection("jdbc:h2:file:" + file.getAbsolutePath() + File.separator + "manage", "root", "abcd@1234");
        return conn;
    }

    public static void initDataBase() throws Exception {
        File file = new File("database");
        if (!file.exists()) {
            file.mkdir();
        }
        Connection conn = getConnection();
        Statement stmt = conn.createStatement();
        String ddlSQL = "create table if not exists DepolyProject (" +
                "ProjectID VARCHAR(100) PRIMARY KEY," +
                "ProjectName VARCHAR(100)," +
                "ServiceName VARCHAR(100)," +
                "JarPackagePath VARCHAR(100)," +
                "DepolyPort VARCHAR(100)," +
                "DepolyBeforeParam VARCHAR(500)," +
                "DepolyAfterParam VARCHAR(500)," +
                "CreateDate VARCHAR(100)," +
                "LastDepolyDate VARCHAR(100))";
        stmt.execute(ddlSQL);
        ddlSQL = "create table if not exists DepolyConfig (" +
                "ConfigID VARCHAR(100) PRIMARY KEY," +
                "ConfigValue VARCHAR(500))";
        stmt.execute(ddlSQL);
        ddlSQL = "create table if not exists DepolyProxy (" +
                "ProxyID VARCHAR(100) PRIMARY KEY," +
                "ProxyName VARCHAR(100)," +
                "ProxyRoutePattern VARCHAR(200)," +
                "ProxyTargetUrl VARCHAR(200)," +
                "ProxyRemark VARCHAR(500))";
        stmt.execute(ddlSQL);
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='DEPENDSERVICE'";
        int count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN DependService VARCHAR(500)";
            stmt.execute(ddlSQL);
        }
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='DEPOLYREMARK'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN DepolyRemark VARCHAR(500)";
            stmt.execute(ddlSQL);
        }


        //war包使用
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='DEPOLYTYPE'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN DepolyType VARCHAR(100)";
            stmt.execute(ddlSQL);
        }
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='WARPORT'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN WarPort VARCHAR(100)";
            stmt.execute(ddlSQL);
        }
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='INITMEMORYSIZE'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN InitMemorySize VARCHAR(100)";
            stmt.execute(ddlSQL);
        }
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='MAXMEMORYSIZE'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN MaxMemorySize VARCHAR(100)";
            stmt.execute(ddlSQL);
        }
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='CONTEXTPATH'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN ContextPath VARCHAR(100)";
            stmt.execute(ddlSQL);
        }
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='SKIPSCAN'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN SkipScan VARCHAR(100)";
            stmt.execute(ddlSQL);
        }
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='SORTNUM'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN SortNum INT";
            stmt.execute(ddlSQL);
        }
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='GROUPGUID'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN GroupGuid VARCHAR(3000)";
            stmt.execute(ddlSQL);
        }

        //防火墙
        ddlSQL = "create table if not exists FireWallRule (" +
                "RuleID VARCHAR(100) PRIMARY KEY," +
                "RuleName VARCHAR(100)," +
                "RuleProtocol VARCHAR(100)," +
                "RulePort VARCHAR(100)," +
                "RuleAction VARCHAR(100));";
        stmt.execute(ddlSQL);

        //脚本
        ddlSQL = "create table if not exists ExecuteCommand (" +
                "CommandID VARCHAR(100) PRIMARY KEY," +
                "ProjectID VARCHAR(100)," +
                "CommandName VARCHAR(100)," +
                "CommandContent CLOB);";
        stmt.execute(ddlSQL);

        //分类
        ddlSQL = "create table if not exists DepolyGroup (" +
                "GroupID VARCHAR(100) PRIMARY KEY," +
                "GroupName VARCHAR(200)," +
                "SortNum INT);";
        stmt.execute(ddlSQL);

        //项目独立jdk路径
        ddlSQL = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='DEPOLYPROJECT' AND COLUMN_NAME='JAVAHOME'";
        count = DataBaseUtil.executeSQLForQueryInt(ddlSQL);
        if (count == 0) {
            ddlSQL = "ALTER TABLE DepolyProject ADD COLUMN JavaHome VARCHAR(1000)";
            stmt.execute(ddlSQL);
        }

        //上传版本控制
        ddlSQL = "create table if not exists DepolyUploadVersion (" +
                "VersionID VARCHAR(100) PRIMARY KEY," +
                "ProjectID VARCHAR(100)," +
                "VersionName VARCHAR(200)," +
                "VersionPath VARCHAR(500)," +
                "ForceKeep VARCHAR(500)," +
                "VersionRemark VARCHAR(500))";
        stmt.execute(ddlSQL);

        stmt.close();
        conn.close();
    }

    public static void executeSQL(String sql) {
        try {
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            stmt.execute(sql);
            stmt.close();
            conn.close();
        } catch (Exception e) {
            LogUtil.error(e);
        }
    }

    public static void executeSQL(String sql, List<String> params) {
        try {
            Connection conn = getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }
            stmt.execute();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            LogUtil.error(e);
        }
    }

    public static int executeSQLForQueryInt(String sql) {
        int result = -1;
        try {
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                result = resultSet.getInt(1);
            }
            stmt.close();
            conn.close();
        } catch (Exception e) {
            LogUtil.error(e);
        }
        return result;
    }

    public static <T> T selectOne(Class<T> clazz, String tableName, String filedName, String fieldValue) {
        String sql = "select * from " + tableName + " where " + filedName + "='" + fieldValue + "'";
        T entity = null;
        try {
            List<Field> fs = Arrays.asList(clazz.getDeclaredFields());
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                entity = clazz.newInstance();
                for (Field f : fs) {
                    f.setAccessible(true);
                    Object value = resultSet.getObject(f.getName());
                    if (value != null && value.getClass() == JdbcClob.class) {
                        Clob clob = resultSet.getClob(f.getName());
                        Reader reader = clob.getCharacterStream();
                        // 读取CLOB数据
                        char[] buffer = new char[1024];
                        StringBuilder out = new StringBuilder();
                        int read;
                        while ((read = reader.read(buffer, 0, buffer.length)) != -1) {
                            out.append(buffer, 0, read);
                        }
                        String clobData = out.toString();
                        f.set(entity, clobData);
                    } else {
                        f.set(entity, value);
                    }
                }
                break;
            }
        } catch (Exception e) {
            LogUtil.error(e);
        }
        return entity;
    }

    public static <T> List<T> selectList(Class<T> clazz, String tableName, String where) {
        String sql = "select * from " + tableName + " where " + where;
        List<T> entityList = new ArrayList<T>();
        try {
            List<Field> fs = Arrays.asList(clazz.getDeclaredFields());
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                T entity = clazz.newInstance();
                for (Field f : fs) {
                    f.setAccessible(true);
                    Object value = resultSet.getObject(f.getName());
                    if (value != null && value.getClass() == JdbcClob.class) {
                        f.set(entity, ConvertUtil.convert2String(value));
                    } else {
                        f.set(entity, value);
                    }

                }
                entityList.add(entity);
            }
        } catch (Exception e) {
            LogUtil.error(e);
        }
        return entityList;
    }

    public static <T> List<T> selectList(Class<T> clazz, String tableName, String where, String orderBy) {
        String sql = "select * from " + tableName + " where " + where + " order by " + orderBy;
        List<T> entityList = new ArrayList<T>();
        try {
            List<Field> fs = Arrays.asList(clazz.getDeclaredFields());
            Connection conn = getConnection();
            Statement stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                T entity = clazz.newInstance();
                for (Field f : fs) {
                    f.setAccessible(true);
                    Object value = resultSet.getObject(f.getName());
                    if (value != null && value.getClass() == JdbcClob.class) {
                        f.set(entity, ConvertUtil.convert2String(value));
                    } else {
                        f.set(entity, value);
                    }

                }
                entityList.add(entity);
            }
        } catch (Exception e) {
            LogUtil.error(e);
        }
        return entityList;
    }

    public static <T> void insert(String tableName, T entity) {
        SqlModel sqlModel = SQLBuilder.buildInsertSQL(tableName, entity);
        executeSQL(sqlModel.getSql(), sqlModel.getParams());
    }

    public static <T> void update(String tableName, T entity, String fieldName) {
        SqlModel sqlModel = SQLBuilder.buildUpdateSQL(tableName, entity, fieldName);
        executeSQL(sqlModel.getSql(), sqlModel.getParams());
    }
}
